﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DataCoreTechnologies.SqlServer
{
    public sealed partial class Database
    {
        private void Load2005_Schemas()
        {
            _schemas.Clear();

            string sql = @"select * from sys.schemas order by schema_id";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var schema = Factory.GetSchema(this);
                schema.Name = Convert.ToString(reader["name"]);
                schema.Id = Convert.ToInt32(reader["schema_id"]);
                AddSchema(schema);

                //Associate the owner of this schema
                if (Convert.ToInt32(reader["schema_id"]) != Convert.ToInt32(reader["principal_id"]))
                {

                    var owner = (from schemaIndex in Schemas
                                 where schemaIndex.Id == Convert.ToInt32(reader["principal_id"])
                                 select schemaIndex).Single<Schema>();
                    schema.Owner = owner;
                }
            }
            reader.Close();
        }
        private void Load2005_SystemTypes()
        {
            _systemTypes.Clear();

            string sql = @"select *
                            from sys.types
                            where is_user_defined = 0
                                and name <> 'sysname'
                         ";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var systemType = Factory.GetSystemType(this);
                systemType.Name = Convert.ToString(reader["name"]);
                systemType.Id = Convert.ToInt32(reader["system_type_id"]);
                systemType.MaxLength = Convert.ToInt32(reader["max_length"]);
                systemType.Precision = Convert.ToInt32(reader["precision"]);
                systemType.Scale = Convert.ToInt32(reader["scale"]);
                systemType.CollationName = reader["collation_name"] == DBNull.Value ? "" : Convert.ToString(reader["collation_name"]);
                systemType.IsNullable = Convert.ToBoolean(reader["is_nullable"]);

                //Assign a category to this data type
                if (systemType.Name == "bit" || systemType.Name == "tinyint" || systemType.Name == "smallint"
                    || systemType.Name == "int" || systemType.Name == "bigint" || systemType.Name == "numeric"
                    || systemType.Name == "decimal" || systemType.Name == "smallmoney" || systemType.Name == "money")

                    systemType.Category = SystemTypeCategory.ExactNumerics;
                else if (systemType.Name == "float" || systemType.Name == "real")
                    systemType.Category = SystemTypeCategory.ApproximateNumerics;
                else if (systemType.Name == "datetime" || systemType.Name == "smalldatetime" || systemType.Name == "date"
                    || systemType.Name == "time" || systemType.Name == "datetime2" || systemType.Name == "datetimeoffset")
                    systemType.Category = SystemTypeCategory.DateAndTime;
                else if (systemType.Name == "char" || systemType.Name == "varchar" || systemType.Name == "text")
                    systemType.Category = SystemTypeCategory.CharacterStrings;
                else if (systemType.Name == "nchar" || systemType.Name == "nvarchar" || systemType.Name == "ntext")
                    systemType.Category = SystemTypeCategory.UnicodeCharacterStrings;
                else if (systemType.Name == "binary" || systemType.Name == "varbinary" || systemType.Name == "image")
                    systemType.Category = SystemTypeCategory.BinaryStrings;
                else if (systemType.Name == "sql_variant" || systemType.Name == "timestamp"
                  || systemType.Name == "uniqueidentifier" || systemType.Name == "xml")
                    systemType.Category = SystemTypeCategory.OtherDataTypes;
                else if (systemType.Name == "hierarchyid")
                    systemType.Category = SystemTypeCategory.CLRDataTypes;
                else if (systemType.Name == "geometry" || systemType.Name == "geometry")
                    systemType.Category = SystemTypeCategory.SpatialDataTypes;
                else { systemType.Category = SystemTypeCategory.Undefined; }

                AddSystemType(systemType);
            }
            reader.Close();
        }
        private void Load2005_UserTypes()
        {
            foreach (Schema schema in Schemas) { schema.ClearUserTypes(); }

            string sql = @"select *
                            from sys.types
                            where is_user_defined = 1
                         ";
            SqlDataReader reader = Server.GetDataReader(sql);

            while (reader.Read())
            {
                var userType = Factory.GetUserType();
                userType.Name = Convert.ToString(reader["name"]);
                userType.Id = Convert.ToInt32(reader["user_type_id"]);
                userType.MaxLength = Convert.ToInt32(reader["max_length"]);
                userType.Precision = Convert.ToInt32(reader["precision"]);
                userType.Scale = Convert.ToInt32(reader["scale"]);
                userType.CollationName = reader["collation_name"] == DBNull.Value ? "" : Convert.ToString(reader["collation_name"]);
                userType.IsNullable = Convert.ToBoolean(reader["is_nullable"]);

                var rootSystemType = (from systemType in _systemTypes
                                      where systemType.Id == Convert.ToInt32(reader["system_type_id"])
                                      select systemType).Single<SystemType>();
                userType.SystemType = rootSystemType;

                var rootSchema = (from schema in Schemas
                                  where schema.Id == Convert.ToInt32(reader["schema_id"])
                                  select schema).Single<Schema>();
                userType.Schema = rootSchema;
                rootSchema.AddUserType(userType);
            }
            reader.Close();
        }
        private void Load2005_DatabaseObjects()
        {
            string sql = @"select * from sys.objects where type in ('U', 'V', 'TF', 'FN', 'P')";
            SqlDataReader reader = Server.GetDataReader(sql);

            while (reader.Read())
            {
                var rootSchema = (from schema in Schemas
                                  where schema.Id == Convert.ToInt32(reader["schema_id"])
                                  select schema).Single<Schema>();

                string objectType = Convert.ToString(reader["type"]);
                objectType = objectType.Trim();

                switch (objectType)
                {
                    case "U":
                        var table = Factory.GetTable(rootSchema);
                        table.Name = Convert.ToString(reader["name"]);
                        table.CreateDate = Convert.ToDateTime(reader["create_date"]);
                        table.ModifiedDate = Convert.ToDateTime(reader["modify_date"]);
                        table.Id = Convert.ToInt32(reader["object_id"]);
                        table.Type = DatabaseObjectType.UserTable;

                        rootSchema.AddTable(table);
                        break;
                    case "V":
                        var view = Factory.GetView(rootSchema);
                        view.Name = Convert.ToString(reader["name"]);
                        view.CreateDate = Convert.ToDateTime(reader["create_date"]);
                        view.ModifiedDate = Convert.ToDateTime(reader["modify_date"]);
                        view.Id = Convert.ToInt32(reader["object_id"]);
                        view.Type = DatabaseObjectType.UserView;

                        rootSchema.AddView(view);
                        break;
                    case "TF":
                    case "FN":
                        var function = Factory.GetFunction(rootSchema);
                        function.Name = Convert.ToString(reader["name"]);
                        function.CreateDate = Convert.ToDateTime(reader["create_date"]);
                        function.ModifiedDate = Convert.ToDateTime(reader["modify_date"]);
                        function.Id = Convert.ToInt32(reader["object_id"]);
                        function.Type = DatabaseObjectType.Function;
                        if (objectType == "TF")
                            function.FunctionType = FunctionType.TableFunction;
                        else
                            function.FunctionType = FunctionType.ScalarFunction;

                        rootSchema.AddFunction(function);
                        break;
                    case "P":
                        var storedProcedure = Factory.GetStoredProcedure(rootSchema);
                        storedProcedure.Name = Convert.ToString(reader["name"]);
                        storedProcedure.CreateDate = Convert.ToDateTime(reader["create_date"]);
                        storedProcedure.ModifiedDate = Convert.ToDateTime(reader["modify_date"]);
                        storedProcedure.Id = Convert.ToInt32(reader["object_id"]);
                        storedProcedure.Type = DatabaseObjectType.StoredProcedure;

                        rootSchema.AddStoredProcedure(storedProcedure);
                        break;
                }
            }

            reader.Close();
        }
        private void Load2005_Columns()
        {
            string sql = @"select * from sys.columns";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var column = Factory.GetColumn();

                column.Name = Convert.ToString(reader["name"]);
                column.Order = Convert.ToInt32(reader["column_id"]);
                if (reader["collation_name"] == DBNull.Value)
                    column.CollationName = "";
                else
                    column.CollationName = Convert.ToString(reader["collation_name"]);

                column.MaxLength = Convert.ToInt32(reader["max_length"]);
                column.Precision = Convert.ToInt32(reader["precision"]);
                column.Scale = Convert.ToInt32(reader["scale"]);
                column.Id = Convert.ToInt32(reader["column_id"]);

                column.IsComputed = Convert.ToBoolean(reader["is_computed"]);
                column.IsNullable = Convert.ToBoolean(reader["is_nullable"]);
                column.IsPrimaryKey = Convert.ToBoolean(reader["is_identity"]);

                //ToDo LINQ
                foreach (SystemType systemType in SystemTypes)
                {
                    if (systemType.Id == Convert.ToInt32(reader["system_type_id"]))
                    {
                        column.SystemType = systemType;
                        break;
                    }
                }
                foreach (Schema schema in Schemas)
                {
                    foreach (UserType userType in schema.UserTypes)
                    {
                        if (userType.Id == Convert.ToInt32(reader["user_type_id"]))
                        {
                            column.UserType = userType;
                        }
                    }
                    foreach (Table table in schema.Tables)
                    {
                        if (table.Id == Convert.ToInt32(reader["object_id"]))
                        {
                            column.Table = table;
                            table.AddColumn(column);
                            break;
                        }
                    }
                    foreach (View view in schema.Views)
                    {
                        if (view.Id == Convert.ToInt32(reader["object_id"]))
                        {
                            column.View = view;
                            view.AddColumn(column);
                            break;
                        }
                    }
                }
            }
            reader.Close();
        }
        private void Load2005_DefaultValues()
        {
            string sql = @"select * from sys.default_constraints";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                Column rootColumn = (from schema in Schemas
                                     where schema.Id == Convert.ToInt32(reader["schema_id"])
                                     from table in schema.Tables
                                     where table.Id == Convert.ToInt32(reader["parent_object_id"])
                                     from column in table.Columns
                                     where column.Id == Convert.ToInt32(reader["parent_column_id"])
                                     select column).SingleOrDefault();

                if (rootColumn != null)
                {
                    rootColumn.DefaultValue = Factory.GetDefaultValue(rootColumn); 
                    rootColumn.DefaultValue.Name = Convert.ToString(reader["name"]);
                    rootColumn.DefaultValue.Id = Convert.ToInt32(reader["object_id"]);
                    rootColumn.DefaultValue.CreateDate = Convert.ToDateTime(reader["create_date"]);
                    rootColumn.DefaultValue.ModifyDate = Convert.ToDateTime(reader["modify_date"]);

                    string definition = Convert.ToString(reader["definition"]);
                    definition = definition.Trim();
                    while (definition.StartsWith("(") && definition.EndsWith(")"))
                        definition = definition.Substring(1, definition.Length - 2);

                    rootColumn.DefaultValue.Formula = definition;
                }
            }
            reader.Close();
        }
        private void Load2005_Indexes()
        {
            //Load the indexes
            string sql = @"
                            select si.*, so.create_date, so.modify_date
                            from sys.indexes si
                            inner join sys.objects so on so.object_id = si.object_id
                            where si.type <> 0
                          ";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {

                Index index = null;

                Table indexTable = (from schema in Schemas
                                    from table in schema.Tables
                                    where table.Id == Convert.ToInt32(reader["object_id"])
                                    select table).SingleOrDefault();

                if (indexTable != null)
                {
                    index = Factory.GetIndex(indexTable);
                    indexTable.AddIndex(index);
                }

                if (index == null)
                {
                    View indexView = (from schema in Schemas
                                      from view in schema.Views
                                      where view.Id == Convert.ToInt32(reader["object_id"])
                                      select view).SingleOrDefault();
                    if (indexView != null)
                    {
                        index = Factory.GetIndex(indexView);
                        indexView.AddIndex(index);
                    }
                }

                if (index != null)
                {
                    index.Name = Convert.ToString(reader["name"]);
                    index.Id = Convert.ToInt32(reader["index_id"]);
                    if (Convert.ToInt32(reader["type"]) == 1)
                        index.Type = IndexType.Clustered;
                    else if (Convert.ToInt32(reader["type"]) == 2)
                        index.Type = IndexType.Nonclustered;
                    else if (Convert.ToInt32(reader["type"]) == 3)
                        index.Type = IndexType.XML;
                    else if (Convert.ToInt32(reader["type"]) == 4)
                        index.Type = IndexType.Spacial;
                    else
                        index.Type = IndexType.Undefined;
                    index.IsUnique = Convert.ToBoolean(reader["is_unique"]);
                    index.IgnoreDuplicateKey = Convert.ToBoolean(reader["ignore_dup_key"]);
                    index.IsPrimaryKey = Convert.ToBoolean(reader["is_primary_key"]);
                    index.IsUniqueConstraint = Convert.ToBoolean(reader["is_unique_constraint"]);
                    index.IsDisabled = Convert.ToBoolean(reader["is_disabled"]);
                    index.AllowRowLocks = Convert.ToBoolean(reader["allow_row_locks"]);
                    index.AllowPageLocks = Convert.ToBoolean(reader["allow_page_locks"]);
                    index.CreationDate = Convert.ToDateTime(reader["create_date"]);
                    index.ModifyDate = Convert.ToDateTime(reader["modify_date"]);
                }
            }
            reader.Close();

            //followed by the Index Columns
            sql = @"select * from sys.index_columns";
            reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                Index rootIndex = (from schema in Schemas
                                   from table in schema.Tables
                                   where table.Id == Convert.ToInt32(reader["object_id"])
                                   from index in table.Indexes
                                   where index.Id == Convert.ToInt32(reader["index_id"])
                                   select index).SingleOrDefault();
                if (rootIndex == null)
                {
                    rootIndex = (from schema in Schemas
                                 from view in schema.Views
                                 where view.Id == Convert.ToInt32(reader["object_id"])
                                 from index in view.Indexes
                                 where index.Id == Convert.ToInt32(reader["index_id"])
                                 select index).SingleOrDefault();
                }

                if (rootIndex != null)
                {
                    var indexColumn = Factory.GetIndexColumn(rootIndex);
                    indexColumn.IsDescending = Convert.ToBoolean(reader["is_descending_key"]);
                    indexColumn.IsIncluded = Convert.ToBoolean(reader["is_included_column"]);
                    indexColumn.Order = Convert.ToInt32(reader["key_ordinal"]);

                    if (rootIndex.Table != null)
                    {
                        indexColumn.Column = (from schema in Schemas
                                              from table in schema.Tables
                                              where table.Id == Convert.ToInt32(reader["object_id"])
                                              from column in table.Columns
                                              where column.Id == Convert.ToInt32(reader["index_column_id"])
                                              select column).SingleOrDefault();

                        if (indexColumn.Column != null) rootIndex.AddIndexColumn(indexColumn);
                    }
                    else
                    {
                        indexColumn.Column = (from schema in Schemas
                                              from view in schema.Views
                                              where view.Id == Convert.ToInt32(reader["object_id"])
                                              from column in view.Columns
                                              where column.Id == Convert.ToInt32(reader["index_column_id"])
                                              select column).SingleOrDefault();

                        if (indexColumn.Column != null) rootIndex.AddIndexColumn(indexColumn);
                    }
                }
            }
            reader.Close();
        }
        private void Load2005_PrimaryKeys()
        {
            string sql = @"select * from sys.objects where type = 'PK'";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                Table rootTable = (from schema in Schemas
                                   where schema.Id == Convert.ToInt32(reader["schema_id"])
                                   from table in schema.Tables
                                   where table.Id == Convert.ToInt32(reader["parent_object_id"])
                                   select table).SingleOrDefault();
                if (rootTable != null)
                {
                    rootTable.PrimaryKey = Factory.GetPrimaryKey(rootTable);
                    rootTable.PrimaryKey.Id = Convert.ToInt32(reader["object_id"]);
                    rootTable.PrimaryKey.Name = Convert.ToString(reader["name"]);
                    rootTable.PrimaryKey.CreateDate = Convert.ToDateTime(reader["create_date"]);
                    rootTable.PrimaryKey.ModifyDate = Convert.ToDateTime(reader["modify_date"]);
                    rootTable.PrimaryKey.IsMicrosoftShipped = Convert.ToBoolean(reader["is_ms_shipped"]);

                    Index rootIndex = (from schema in Schemas
                                       where schema.Id == Convert.ToInt32(reader["schema_id"])
                                       from table in schema.Tables
                                       where table.Id == Convert.ToInt32(reader["parent_object_id"])
                                       from index in table.Indexes
                                       where index.IsPrimaryKey == true
                                       select index).SingleOrDefault();
                    if (rootIndex != null)
                    {
                        rootIndex.PrimaryKey = rootTable.PrimaryKey;
                        rootTable.PrimaryKey.Index = rootIndex;
                    }
                }
            }
            reader.Close();
        }
        private void Load2005_ForeignKeys()
        {
            //First load the foreign keys
            string sql = @"select * from sys.foreign_keys";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                Table rootTable = (from schema in Schemas
                                   where schema.Id == Convert.ToInt32(reader["schema_id"])
                                   from table in schema.Tables
                                   where table.Id == Convert.ToInt32(reader["parent_object_id"])
                                   select table).SingleOrDefault();

                if (rootTable != null)
                {
                    var foreignKey = Factory.GetForeignKey(rootTable); 
                    foreignKey.Id = Convert.ToInt32(reader["object_id"]);
                    foreignKey.Name = Convert.ToString(reader["name"]);
                    foreignKey.IsDisabled = Convert.ToBoolean(reader["is_disabled"]);
                    foreignKey.IsNotTrusted = Convert.ToBoolean(reader["is_not_trusted"]);
                    foreignKey.IsSystemNamed = Convert.ToBoolean(reader["is_system_named"]);
                    foreignKey.DeleteReferentialActionDescription = Convert.ToString(reader["delete_referential_action_desc"]);
                    foreignKey.UpdateReferentialActionDescription = Convert.ToString(reader["update_referential_action_desc"]);

                    rootTable.AddForeignKey(foreignKey);
                }
            }
            reader.Close();

            //then associated any necessary columns
            sql = @"select * from sys.foreign_key_columns";
            reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                ForeignKey rootForeignKey = (from schema in Schemas
                                             from table in schema.Tables
                                             from foreignKey in table.ForeignKeys
                                             where foreignKey.Id == Convert.ToInt32(reader["constraint_object_id"])
                                             select foreignKey).SingleOrDefault();
                if (rootForeignKey != null)
                {
                    Column parentColumn = (from schema in Schemas
                                           from table in schema.Tables
                                           where table.Id == Convert.ToInt32(reader["parent_object_id"])
                                           from column in table.Columns
                                           where column.Id == Convert.ToInt32(reader["parent_column_id"])
                                           select column).SingleOrDefault();
                    Column referenceColumn = (from schema in Schemas
                                              from table in schema.Tables
                                              where table.Id == Convert.ToInt32(reader["referenced_object_id"])
                                              from column in table.Columns
                                              where column.Id == Convert.ToInt32(reader["referenced_column_id"])
                                              select column).SingleOrDefault();
                    
                    parentColumn.IsForeignKey = true;
                    rootForeignKey.AddColumns(Factory.GetForeignKeyColumns(parentColumn, referenceColumn));
                }
            }
            reader.Close();
        }
        private void Load2005_CheckConstraints()
        {
            string sql = @"select * from sys.check_constraints";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                Column rootColumn = (from schema in Schemas
                                     where schema.Id == Convert.ToInt32(reader["schema_id"])
                                     from table in schema.Tables
                                     where table.Id == Convert.ToInt32(reader["parent_object_id"])
                                     from column in table.Columns
                                     where column.Id == Convert.ToInt32(reader["parent_column_id"])
                                     select column).SingleOrDefault();

                if (rootColumn != null)
                {
                    rootColumn.CheckConstraint = Factory.GetCheckConstraint(rootColumn);

                    rootColumn.CheckConstraint.Id = Convert.ToInt32(reader["object_id"]);
                    rootColumn.CheckConstraint.Name = Convert.ToString(reader["name"]);

                    string definition = Convert.ToString(reader["definition"]);
                    definition = definition.Trim();
                    while (definition.StartsWith("(") && definition.EndsWith(")"))
                    {
                        definition = definition.Substring(1, definition.Length - 2);
                    }
                    rootColumn.CheckConstraint.Formula = definition;
                    rootColumn.CheckConstraint.CreateDate = Convert.ToDateTime(reader["create_date"]);
                    rootColumn.CheckConstraint.ModifyDate = Convert.ToDateTime(reader["modify_date"]);
                    rootColumn.CheckConstraint.IsMicrosoftShipped = Convert.ToBoolean(reader["is_ms_shipped"]);
                    rootColumn.CheckConstraint.IsPublished = Convert.ToBoolean(reader["is_published"]);
                    rootColumn.CheckConstraint.IsSchemaPublished = Convert.ToBoolean(reader["is_schema_published"]);
                    rootColumn.CheckConstraint.IsDisabled = Convert.ToBoolean(reader["is_disabled"]);
                }
            }
            reader.Close();
        }
        private void Load2005_Parameters()
        {
            string sql = @"select * from sys.parameters";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                StoredProcedure rootStoredProcedure = (from schema in Schemas
                                                       from storedProcedure in schema.StoredProcedures
                                                       where storedProcedure.Id == Convert.ToInt32(reader["object_id"])
                                                       select storedProcedure).SingleOrDefault();
                Function rootFunction = null;

                if (rootStoredProcedure == null)
                {
                    rootFunction = (from schema in Schemas
                                    from function in schema.Functions
                                    where function.Id == Convert.ToInt32(reader["object_id"])
                                    select function).SingleOrDefault();
                }

                Parameter parameter = null;
                if (rootStoredProcedure != null)
                {
                    parameter = Factory.GetParameter(rootStoredProcedure);
                    rootStoredProcedure.AddParameter(parameter);
                }
                else if (rootFunction != null)
                {
                    parameter = Factory.GetParameter(rootFunction);
                    rootFunction.AddParameter(parameter);
                }

                if (parameter != null)
                {
                    parameter.Id = Convert.ToInt32(reader["parameter_id"]);

                    if (parameter.Id == 0)
                        parameter.Name = "Return Value";
                    else
                        parameter.Name = Convert.ToString(reader["name"]);

                    parameter.Length = Convert.ToInt32(reader["max_length"]);
                    parameter.Precision = Convert.ToInt32(reader["precision"]);
                    parameter.Scale = Convert.ToInt32(reader["scale"]);

                    parameter.Order = Convert.ToInt32(reader["parameter_id"]);

                    parameter.IsOutput = Convert.ToBoolean(reader["is_output"]);
                    parameter.IsCursorReference = Convert.ToBoolean(reader["is_cursor_ref"]);
                    if (reader["default_value"] == DBNull.Value)
                        parameter.DefaultValue = "";
                    else
                        parameter.DefaultValue = Convert.ToString(reader["default_value"]);

                    parameter.SystemType = (from systemType in SystemTypes
                                            where systemType.Id == Convert.ToInt32(reader["system_type_id"])
                                            select systemType).SingleOrDefault();

                    parameter.UserType = (from schema in Schemas
                                          from customType in schema.UserTypes
                                          where customType.Id == Convert.ToInt32(reader["user_type_id"])
                                          select customType).SingleOrDefault();
                }

            }
            reader.Close();
        }
        private void Load2005_Triggers()
        {
            string sql = @"select 
	                            st.parent_class, st.parent_id, st.name, st.object_id, st.type,
                                st.create_date, st.modify_date, st.is_ms_shipped, 
                                st.is_disabled, st.is_instead_of_trigger,
                                CASE WHEN ted.type IS NULL THEN 0 ELSE 1 END AS IsDelete,
                                CASE WHEN teu.type IS NULL THEN 0 ELSE 1 END AS IsUpdate,
                                CASE WHEN tei.type IS NULL THEN 0 ELSE 1 END AS IsInsert,
                                ISNULL(smtr.definition, ssmtr.definition) AS [Definition]
                            from sys.triggers st
                            LEFT OUTER JOIN sys.trigger_events AS tei ON tei.object_id = st.object_id and tei.type=1
                            LEFT OUTER JOIN sys.trigger_events AS teu ON teu.object_id = st.object_id and teu.type=2
                            LEFT OUTER JOIN sys.trigger_events AS ted ON ted.object_id = st.object_id and ted.type=3
                            LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = st.object_id
                            LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = st.object_id
                            ";
            SqlDataReader reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                Trigger trigger = null;
                //First check to see if this is a database trigger
                if (Convert.ToInt32(reader["parent_class"]) == 0)
                {
                    trigger = Factory.GetTrigger(this);
                    AddTrigger(trigger);
                    trigger.TriggerOwner = TriggerOwner.Database;
                }
                else
                {
                    //Then let's see if it's defined against a table
                    Table rootTable = (from schema in Schemas
                                       from table in schema.Tables
                                       where table.Id == Convert.ToInt32(reader["parent_id"])
                                       select table).SingleOrDefault();
                    if (rootTable != null)
                    {
                       trigger = Factory.GetTrigger(rootTable);
                       rootTable.AddTrigger(trigger);
                       trigger.TriggerOwner = TriggerOwner.Table;
                    }
                    else
                    {
                        //Finally check the views
                        View rootView = (from schema in Schemas
                                         from view in schema.Views
                                         where view.Id == Convert.ToInt32(reader["parent_id"])
                                         select view).SingleOrDefault();
                        if (rootView != null)
                        {
                           trigger = Factory.GetTrigger(rootView);
                           rootView.AddTrigger(trigger);
                           trigger.TriggerOwner = TriggerOwner.View;
                        }
                    }
                }

                if (trigger != null)
                {
                    trigger.Name = (string)reader["name"];
                    trigger.Id = Convert.ToInt32(reader["object_id"]);
                    if (Convert.ToString(reader["type"]) == "TR")
                        trigger.TriggerType = TriggerType.SqlTrigger;
                    else if (Convert.ToString(reader["type"]) == "TA")
                        trigger.TriggerType = TriggerType.CLRTrigger;
                    else
                        trigger.TriggerType = TriggerType.Undefined;
                    trigger.CreateDate = Convert.ToDateTime(reader["create_date"]);
                    trigger.ModifyDate = Convert.ToDateTime(reader["modify_date"]);
                    trigger.IsMicrosoftShipped = Convert.ToBoolean(reader["is_ms_shipped"]);
                    trigger.IsDisabled = Convert.ToBoolean(reader["is_disabled"]);
                    trigger.IsInsteadOfTrigger = Convert.ToBoolean(reader["is_instead_of_trigger"]);
                    trigger.IsDelete = Convert.ToBoolean(reader["IsDelete"]);
                    trigger.IsInsert = Convert.ToBoolean(reader["IsInsert"]);
                    trigger.IsUpdate = Convert.ToBoolean(reader["IsUpdate"]);
                    //Load the DDL
                    trigger.DDL = Convert.ToString(reader["Definition"]);
                }
            }
            reader.Close();
        }
        private void Load2005_DatabaseSize()
        {
            string sql = "sp_spaceused";
            SqlDataReader reader = Server.GetDataReader(sql);

            reader.NextResult();

            while (reader.Read())
            {
                string data = Convert.ToString(reader["data"]);
                string index = Convert.ToString(reader["index_size"]);
                string avail = Convert.ToString(reader["unused"]);

                data = data.Substring(0, data.Length - 3);
                index = index.Substring(0, index.Length - 3);
                avail = avail.Substring(0, avail.Length - 3);

                DataSpaceUsed = Convert.ToInt64(data);
                IndexSpaceUsed = Convert.ToInt64(index);
                DataSpaceAvailable = Convert.ToInt64(avail);
            }

            reader.Close();
        }
        private void Load2005_TableSizes()
        {
            var tables = (from schema in Schemas
                          from table in schema.Tables
                          select table);
            foreach (Table table in tables)
            {
                string sql = "sp_spaceused '[" + table.Schema.Name + "].[" + table.Name + "]'";
                SqlDataReader reader = Server.GetDataReader(sql);
                while (reader.Read())
                {
                    table.RowCount = Convert.ToInt64(reader["rows"]);

                    string data = Convert.ToString(reader["data"]);
                    string index = Convert.ToString(reader["index_size"]);
                    string avail = Convert.ToString(reader["unused"]);

                    data = data.Substring(0, data.Length - 3);
                    index = index.Substring(0, index.Length - 3);
                    avail = avail.Substring(0, avail.Length - 3);

                    table.DataSpaceUsed = Convert.ToInt64(data);
                    table.IndexSpaceUsed = Convert.ToInt64(index);
                    table.DataSpaceAvailable = Convert.ToInt64(avail);
                }
                reader.Close();
            }
        }
        private void Load2005_StoredProceduresCode()
        {
            string sql = @"
                        select * from sys.syscomments
                        where id in (select object_id from sys.objects where type = 'P')
                        order by id, colid
                          ";
            var sprocs = (from schema in _schemas
                          from sproc in schema.StoredProcedures
                          select sproc);
            foreach (StoredProcedure sproc in sprocs) sproc.DDL = "";

            var reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var matchedSProc = (from schema in _schemas
                                    from sproc in schema.StoredProcedures
                                    where sproc.Id == Convert.ToInt32(reader["id"])
                                    select sproc).Single();
                matchedSProc.DDL += Convert.ToString(reader["text"]);
            }
            reader.Close();
            //var storedProcedures = (from schema in Schemas
            //                        from storedProcedure in schema.StoredProcedures
            //                        select storedProcedure);
            //foreach (StoredProcedure storedProcedure in storedProcedures)
            //{
            //    sql = @"sp_helptext '[" + storedProcedure.Schema.Name +
            //        @"].[" + storedProcedure.Name + @"]'";
            //    reader = Server.GetDataReader(sql);
            //    while (reader.Read())
            //    {
            //        string code = Convert.ToString(reader["Text"]);
            //        code = code.Replace("\r\n", "");
            //        storedProcedure.AddCodeLine(code);
            //    }
            //    reader.Close();
            //}
        }
        private void Load2005_FunctionsCode()
        {
            string sql = @"
                        select * from sys.syscomments
                        where id in (select object_id from sys.objects where type in ('FN', 'TF'))
                        order by id, colid
                          ";
            var funcs = (from schema in _schemas
                         from func in schema.Functions
                         select func);
            foreach (Function func in funcs) func.DDL = "";

            var reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var matchedFunc  = (from schema in _schemas
                                    from func in schema.Functions
                                    where func.Id == Convert.ToInt32(reader["id"])
                                    select func).SingleOrDefault();
                if (matchedFunc != null) matchedFunc.DDL += Convert.ToString(reader["text"]);
            }
            reader.Close();
            //var functions = (from schema in Schemas
            //                 from function in schema.Functions
            //                 select function);
            //foreach (Function function in functions)
            //{
            //    string sql = @"sp_helptext '[" + function.Schema.Name +
            //        @"].[" + function.Name + @"]'";
            //    SqlDataReader reader = Server.GetDataReader(sql);
            //    while (reader.Read())
            //    {
            //        string code = Convert.ToString(reader["Text"]);
            //        code = code.Replace("\r\n", "");
            //        function.AddCodeLine(code);
            //    }
            //    reader.Close();
            //}
        }
        private void Load2005_ViewsCode()
        {
            string sql = @"
                        select * from sys.syscomments
                        where id in (select object_id from sys.objects where type = 'V')
                        order by id, colid
                          ";
            var views = (from schema in _schemas
                         from view in schema.Views
                         select view);
            foreach (View view in views) view.DDL = "";

            var reader = Server.GetDataReader(sql);
            while (reader.Read())
            {
                var matchedView = (from schema in _schemas
                                   from view in schema.Views
                                   where view.Id == Convert.ToInt32(reader["id"])
                                   select view).Single();
                matchedView.DDL += Convert.ToString(reader["text"]);
            }
            reader.Close();
        }
        private void Load2005_ExtendedProperties()
        {
            string sql = "select * from sys.extended_properties order by class, major_id, minor_id";
            SqlDataReader reader = Server.GetDataReader(sql);

            int missedCount = 0;
            while (reader.Read())
            {
                var extProperty = Factory.GetExtendedProperty(Convert.ToString(reader["name"]),
                                                              Convert.ToString(reader["value"]));
                //Database properties
                if (Convert.ToInt32(reader["class"]) == 0)
                {
                    if (Convert.ToString(reader["name"]) == "MS_Description")
                        Description = Convert.ToString(reader["value"]);

                    extProperty.Database = this;
                    _extendedProperties.Add(extProperty);
                }
                else if (Convert.ToInt32(reader["minor_id"]) == 0)      //Sys.Objects
                {
                    Boolean foundMatch = false;
                    #region Schema
                    if (!foundMatch)
                    {
                        var matchedSchema = (from schema in Schemas
                                             where schema.Id == Convert.ToInt32(reader["major_id"])
                                             select schema).SingleOrDefault();
                        if (matchedSchema != null)
                        {
                            if (Convert.ToString(reader["name"]) == "MS_Description")
                                matchedSchema.Description = Convert.ToString(reader["value"]);

                            extProperty.Schema = matchedSchema;
                            matchedSchema.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    #region Table
                    if (!foundMatch)
                    {
                        var matchedTable = (from schema in Schemas
                                            from table in schema.Tables
                                            where table.Id == Convert.ToInt32(reader["major_id"])
                                            select table).SingleOrDefault();
                        if (matchedTable != null)
                        {
                            if (Convert.ToString(reader["name"]) == "MS_Description")
                                matchedTable.Description = Convert.ToString(reader["value"]);
                            extProperty.Table = matchedTable;
                            matchedTable.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    #region View
                    if (!foundMatch)
                    {
                        var matchedView = (from schema in Schemas
                                           from view in schema.Views
                                           where view.Id == Convert.ToInt32(reader["major_id"])
                                           select view).SingleOrDefault();
                        if (matchedView != null)
                        {
                            if (Convert.ToString(reader["name"]) == "MS_Description")
                                matchedView.Description = Convert.ToString(reader["value"]);
                            extProperty.View = matchedView;
                            matchedView.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    #region Stored Procedure
                    if (!foundMatch)
                    {
                        var matchedSProc = (from schema in Schemas
                                            from sproc in schema.StoredProcedures
                                            where sproc.Id == Convert.ToInt32(reader["major_id"])
                                            select sproc).SingleOrDefault();
                        if (matchedSProc != null)
                        {
                            if (Convert.ToString(reader["name"]) == "MS_Description")
                                matchedSProc.Description = Convert.ToString(reader["value"]);
                            extProperty.StoredProcedure = matchedSProc;
                            matchedSProc.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    #region Function
                    if (!foundMatch)
                    {
                        var matchedFunction = (from schema in Schemas
                                               from function in schema.Functions
                                               where function.Id == Convert.ToInt32(reader["major_id"])
                                               select function).SingleOrDefault();
                        if (matchedFunction != null)
                        {
                            if (Convert.ToString(reader["name"]) == "MS_Description")
                                matchedFunction.Description = Convert.ToString(reader["value"]);
                            extProperty.Function = matchedFunction;
                            matchedFunction.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    #region DefaultValues
                    if (!foundMatch)
                    {
                        var defaultValue = (from schema in Schemas
                                            from table in schema.Tables
                                            from column in table.Columns
                                            where column.DefaultValue != null
                                                && column.DefaultValue.Id == Convert.ToInt32(reader["major_id"])
                                            select column.DefaultValue).SingleOrDefault();
                        if (defaultValue != null)
                        {
                            extProperty.DefaultValue = defaultValue;
                            defaultValue.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    #region Foreign Keys
                    if (!foundMatch)
                    {
                        var matchedKey = (from schema in Schemas
                                          from table in schema.Tables
                                          from foreignKey in table.ForeignKeys
                                          where foreignKey.Id == Convert.ToInt32(reader["major_id"])
                                          select foreignKey).SingleOrDefault();
                        if (matchedKey != null)
                        {
                            extProperty.ForeignKey = matchedKey;
                            matchedKey.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    #region CheckConstraints
                    if (!foundMatch)
                    {
                        var matchedCheck = (from schema in Schemas
                                            from table in schema.Tables
                                            from column in table.Columns
                                            where column.CheckConstraint != null
                                                && column.CheckConstraint.Id == Convert.ToInt32(reader["major_id"])
                                            select column.CheckConstraint).SingleOrDefault();
                        if (matchedCheck != null)
                        {
                            extProperty.CheckConstraint = matchedCheck;
                            matchedCheck.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    #region Primary Keys
                    if (!foundMatch)
                    {
                        var matchedPrimaryKey = (from schema in Schemas
                                                 from table in schema.Tables
                                                 where table.PrimaryKey != null
                                                    && table.PrimaryKey.Id == Convert.ToInt32(reader["major_id"])
                                                 select table.PrimaryKey).SingleOrDefault();
                        if (matchedPrimaryKey != null)
                        {
                            extProperty.PrimaryKey = matchedPrimaryKey;
                            matchedPrimaryKey.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    #region Triggers
                    if (!foundMatch)
                    {
                        var matchedTrigger = (from trigger in _triggers
                                              where trigger.Id == Convert.ToInt32(reader["major_id"])
                                              select trigger).SingleOrDefault();
                        if (matchedTrigger != null)
                        {
                            if (Convert.ToString(reader["name"]) == "MS_Description")
                                matchedTrigger.Description = Convert.ToString(reader["value"]);
                            extProperty.Trigger = matchedTrigger;
                            matchedTrigger.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    if (!foundMatch)
                    {
                        var matchedTrigger = (from schema in _schemas
                                              from table in schema.Tables
                                              from trigger in table.Triggers
                                              where trigger.Id == Convert.ToInt32(reader["major_id"])
                                              select trigger).SingleOrDefault();
                        if (matchedTrigger != null)
                        {
                            if (Convert.ToString(reader["name"]) == "MS_Description")
                                matchedTrigger.Description = Convert.ToString(reader["value"]);
                            extProperty.Trigger = matchedTrigger;
                            matchedTrigger.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    if (!foundMatch)
                    {
                        var matchedTrigger = (from schema in _schemas
                                              from view in schema.Views
                                              from trigger in view.Triggers
                                              where trigger.Id == Convert.ToInt32(reader["major_id"])
                                              select trigger).SingleOrDefault();
                        if (matchedTrigger != null)
                        {
                            if (Convert.ToString(reader["name"]) == "MS_Description")
                                matchedTrigger.Description = Convert.ToString(reader["value"]);
                            extProperty.Trigger = matchedTrigger;
                            matchedTrigger.AddExtendedProperty(extProperty);
                            foundMatch = true;
                        }
                    }
                    #endregion
                    if (!foundMatch)
                    {
                        missedCount++;
                        System.Diagnostics.Debug.WriteLine("Unknown extended property id: " + Convert.ToString(reader["major_id"]));
                    }
                }
                else
                {
                    Boolean foundMatch = false;
                    #region Indexes
                    if (!foundMatch)
                    {
                        if (Convert.ToInt32(reader["class"]) == 7)
                        {
                            //Tables
                            var matchedIndex = (from schema in _schemas
                                                from table in schema.Tables
                                                where table.Id == Convert.ToInt32(reader["major_id"])
                                                from index in table.Indexes
                                                where index.Id == Convert.ToInt32(reader["minor_id"])
                                                select index).SingleOrDefault();
                            if (matchedIndex != null)
                            {
                                if (Convert.ToString(reader["name"]) == "MS_Description")
                                    matchedIndex.Description = Convert.ToString(reader["value"]);
                                extProperty.Index = matchedIndex;
                                matchedIndex.AddExtendedProperty(extProperty);
                                foundMatch = true;
                            }
                            //Views
                            if (!foundMatch)
                            {
                                matchedIndex = (from schema in _schemas
                                                from view in schema.Views
                                                where view.Id == Convert.ToInt32(reader["major_id"])
                                                from index in view.Indexes
                                                where index.Id == Convert.ToInt32(reader["minor_id"])
                                                select index).SingleOrDefault();
                                if (matchedIndex != null)
                                {
                                    if (Convert.ToString(reader["name"]) == "MS_Description")
                                        matchedIndex.Description = Convert.ToString(reader["value"]);
                                    extProperty.Index = matchedIndex;
                                    matchedIndex.AddExtendedProperty(extProperty);
                                    foundMatch = true;
                                }
                            }
                        }
                    }
                    #endregion
                    #region Columns
                    if (!foundMatch)
                    {
                        if (Convert.ToInt32(reader["class"]) == 1)
                        {
                            var matchedColumn = (from schema in _schemas
                                                 from table in schema.Tables
                                                 where table.Id == Convert.ToInt32(reader["major_id"])
                                                 from column in table.Columns
                                                 where column.Id == Convert.ToInt32(reader["minor_id"])
                                                 select column).SingleOrDefault();
                            if (matchedColumn != null)
                            {
                                if (Convert.ToString(reader["name"]) == "MS_Description")
                                    matchedColumn.Description = Convert.ToString(reader["value"]);
                                extProperty.Column = matchedColumn;
                                matchedColumn.AddExtendedProperty(extProperty);
                                foundMatch = true;
                            }
                        }
                    }
                    #endregion
                    #region Parameters
                    if (!foundMatch)
                    {
                        if (Convert.ToInt32(reader["class"]) == 2)
                        {
                            var matchedParam = (from schema in _schemas
                                                from storedProcedure in schema.StoredProcedures
                                                where storedProcedure.Id == Convert.ToInt32(reader["major_id"])
                                                from parameter in storedProcedure.Parameters
                                                where parameter.Id == Convert.ToInt32(reader["minor_id"])
                                                select parameter).SingleOrDefault();
                            if (matchedParam != null)
                            {
                                if (Convert.ToString(reader["name"]) == "MS_Description")
                                    matchedParam.Description = Convert.ToString(reader["value"]);
                                foundMatch = true;
                            }
                        }
                    }
                    if (!foundMatch)
                    {
                        if (Convert.ToInt32(reader["class"]) == 2)
                        {
                            var matchedParam = (from schema in _schemas
                                                from function in schema.Functions
                                                where function.Id == Convert.ToInt32(reader["major_id"])
                                                from parameter in function.Parameters
                                                where parameter.Id == Convert.ToInt32(reader["minor_id"])
                                                select parameter).SingleOrDefault();
                            if (matchedParam != null)
                            {
                                if (Convert.ToString(reader["name"]) == "MS_Description")
                                    matchedParam.Description = Convert.ToString(reader["value"]);
                                foundMatch = true;
                            }
                        }
                    }

                    #endregion
                }
            }
            System.Diagnostics.Debug.WriteLine("Total Error:" + missedCount.ToString());
            reader.Close();
        }
        private void Load2005_Dependencies()
        {
            string sql = @"select * from sys.sql_dependencies";
            var reader = Server.GetDataReader(sql);
            while (reader.Read())
            {

            }
            reader.Close();
        }
    }
}
